package daos.user.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.entities.User;
import daos.AbstractDAO;
import daos.db.Conexion;
import daos.user.UserAppDAO;

public class UserAppDAOImpl extends AbstractDAO implements UserAppDAO {

    Conexion con = new Conexion();

    public void insert(User user) throws SQLException {
        String insertSql = "INSERT INTO USERS (NAME, LOGIN, PASSWORD) VALUES (?,?,?)";
        PreparedStatement stmt = null;
        Connection conex = null;
        try {

            conex = con.getConnected();
            stmt = conex.prepareStatement(insertSql);

            stmt.setString(1, user.getName());
            stmt.setString(2, user.getLogin());
            stmt.setString(3, user.getPassword());
            System.out.println(insertSql);

            stmt.executeUpdate();
            System.out.println("Row inserted OK");

        } finally {
            closeConnection(conex, stmt, null);
        }
    }

    public void delete(User user) throws SQLException {
        String deleteSql = "DELETE FROM USERS WHERE NAME = ?";
        PreparedStatement stmt = null;
        Connection conex = null;
        try {

            conex = con.getConnected();
            stmt = conex.prepareStatement(deleteSql);

            stmt.setString(1, user.getName());
            System.out.println(deleteSql);

            stmt.executeUpdate();
            System.out.println("Row deleted OK");

        } finally {
            closeConnection(conex, stmt, null);
        }

    }

    public void update(User user) throws SQLException {
        String updateSql = "UPDATE USERS SET NAME = ?, PASSWORD = ? WHERE LOGIN = ?";
        PreparedStatement stmt = null;
        Connection conex = null;
        try {

            conex = con.getConnected();
            stmt = conex.prepareStatement(updateSql);
            stmt.setString(1, user.getName());
            stmt.setString(2, user.getPassword());
            stmt.setString(3, user.getLogin());

            System.out.println(updateSql);

            stmt.executeUpdate();
            System.out.println("Row updated OK");
        } finally {
            closeConnection(conex, stmt, null);
        }
    }
    
    public List<User> selectAll() throws SQLException {
        String selectSql = "SELECT NAME, LOGIN, PASSWORD FROM USERS";
        PreparedStatement stmt = null;
        Connection conex = null;
        ResultSet rs = null;
        List<User> userSeleccionados = null;
        User userSeleccionado = null;

        try {
            conex = con.getConnected();
            stmt = conex.prepareStatement(selectSql);

            System.out.println(selectSql);

            rs = stmt.executeQuery();

            userSeleccionados = new ArrayList<User>();

            while (rs.next()) {
                userSeleccionado = new User();
                userSeleccionado.setName(rs.getString("NAME"));
                userSeleccionado.setLogin(rs.getString("LOGIN"));
                userSeleccionado.setPassword(rs.getString("PASSWORD"));

                userSeleccionados.add(userSeleccionado);
            }

            return userSeleccionados;

        } finally {
            closeConnection(conex, stmt, rs);
        }
    }
    

    public List<User> selectByName(User user) throws SQLException {
        String selectSql = "SELECT * FROM USERS WHERE UPPER(NAME) LIKE ?";
        PreparedStatement stmt = null;
        Connection conex = null;
        ResultSet rs = null;
        List<User> userSeleccionados = null;
        User userSeleccionado = null;
        
        try {            
            conex = con.getConnected();
            stmt = conex.prepareStatement(selectSql);
            stmt.setString(1, "%" + user.getName().toUpperCase() + "%");

            System.out.println(selectSql);

            rs = stmt.executeQuery();

            userSeleccionados = new ArrayList<User>();
            
            while (rs.next()) {
                userSeleccionado = new User();
                userSeleccionado.setName(rs.getString("NAME"));
                userSeleccionado.setLogin(rs.getString("LOGIN"));
                userSeleccionado.setPassword(rs.getString("PASSWORD"));
                
                userSeleccionados.add(userSeleccionado);
            }

            return userSeleccionados;

        } finally {
            closeConnection(conex, stmt, null);
        }
    }
    
    public boolean exist(User user) throws SQLException {
        String selectSql = "SELECT * FROM USERS WHERE LOGIN = ? AND PASSWORD = ?";
        PreparedStatement stmt = null;
        Connection conex = null;
        ResultSet rs = null;
        try {
            conex = con.getConnected();
            stmt = conex.prepareStatement(selectSql);
            stmt.setString(1, user.getLogin());
            stmt.setString(2, user.getPassword());

            System.out.println(selectSql);

            rs = stmt.executeQuery();

            rs.next();

            if(rs.getRow() == 0)
            {
                return false;    
            } else {
                return true;
            }
        } finally {
            closeConnection(conex, stmt, null);
        }
    }
}

